package com.yjxxt.crud;

import com.yjxxt.crud.DButil;
import com.yjxxt.crud.Dept;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

// DAO 数据访问对象
public class DeptDao {
    //insert
    public int addDept(Dept dept) throws Exception {
        //String sql = "insert into dept values(50,'开发部','长沙')";
        String sql = "insert into dept values(?,?,?)";

        //根据Connection创建语句对象
        // Statement statement = DButil.getConnection().createStatement();
        //通过预处理语句对象对？占位符赋值
        Connection connection = DButil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,dept.getDeptno());
        preparedStatement.setString(2,dept.getDname());
        preparedStatement.setString(3,dept.getLoc());

        int r=  preparedStatement.executeUpdate();
        DButil.close(connection,preparedStatement,null);
        return r;
        // return   statement.executeUpdate(sql);
    }

    public int deleteDept(int deptno) throws Exception {
        String sql = "delete from dept where deptno=?";

        Connection connection = DButil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,deptno);

        int r=  preparedStatement.executeUpdate();
        DButil.close(connection,preparedStatement,null);
        return r;
    }


    public List<Dept> findAll() throws Exception{
        String sql = "select * from dept";
        List<Dept> list = new ArrayList<>();

        Connection connection = DButil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int deptno = resultSet.getInt("deptno");
            String dname = resultSet.getString(2);
            String loc = resultSet.getString("loc");

            Dept dept =new Dept();
            dept.setDeptno(deptno);
            dept.setDname(dname);
            dept.setLoc(loc);

            list.add(dept);
        }
        DButil.close(connection,preparedStatement,resultSet);
        return  list;
    }
    //分页查询的参数 当前页数currentPage 、每页条数pageSize
    public List<Dept> findByPage(int currentPage,int pageSize) throws Exception{
        String sql = "select * from dept limit ?,?";
        List<Dept> list = new ArrayList<>();

        Connection connection = DButil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,(currentPage-1)*pageSize);
        preparedStatement.setInt(2,pageSize);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int deptno = resultSet.getInt("deptno");
            String dname = resultSet.getString(2);
            String loc = resultSet.getString("loc");

            Dept dept =new Dept();
            dept.setDeptno(deptno);
            dept.setDname(dname);
            dept.setLoc(loc);

            list.add(dept);
        }
        DButil.close(connection,preparedStatement,resultSet);
        return  list;
    }


    public static void main(String[] args) throws Exception {
        //int row =new DeptDao().addDept(new Dept(50,"测试部","望城"));
        //int row =new DeptDao().deleteDept(50);
        //System.out.println(row);
        List<Dept> all = new DeptDao().findAll();
        List<Dept> page = new DeptDao().findByPage(1,4);
        System.out.println(page);

    }
}
